package es.gesalud.dispensadorpezwebservices.ws;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */


import es.gesalud.dispensadorpezwebservices.data.Record;
import java.sql.*;
import javax.jws.WebService;
import javax.jws.WebMethod;
import javax.jws.WebParam;

/**
 *
 * @author essamu
 */
@WebService(serviceName = "BDinteractions")
public class BDinteractions {
    
    private static String dbURL = "jdbc:derby://localhost:1527/dispensadorDB;create=true;user=smux;password=samuel";
    private static Connection conn = null;
    private static Statement stmt = null;
    
    //Creating the connection
    private static void createConnection()
    {
        try
        {
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            //Get a connection
            conn = DriverManager.getConnection(dbURL); 
        }
        catch (Exception except)
        {
            except.printStackTrace();
        }
    }    
    
    //Shuttingdown the conection
    private static void shutdown()
    {
        try
        {
            if (stmt != null)
            {
                stmt.close();
            }
            if (conn != null)
            {
                DriverManager.getConnection(dbURL + ";shutdown=true");
                conn.close();
            }           
        }
        catch (SQLException sqlExcept)
        {
            
        }

    }

    /**
     * Web service operation 'read'
     * :: This operation retrieve all requests of a owner
     */
    @WebMethod(operationName = "select")
    public Record[] select(@WebParam(name = "idowner") String idowner){
        System.out.println("Beginning DBinteractions.java...");
        Record mainRecord[];

        createConnection();
        mainRecord = selectRequests(idowner);
        shutdown();
    
        return mainRecord;
    }

    private static Record[] selectRequests(String idowner)
        {
            Record mainRecord[]=null;
            
            try
            {
                //Joining the query
                if(idowner.equals(null) || idowner.equals(""))
                    idowner="0";
                String query = "select * from APP.REQUESTS where IDOWNER="+idowner;
                System.out.println("Query: "+query);
                stmt = conn.createStatement();
                ResultSet results = stmt.executeQuery(query);
                ResultSetMetaData rsmd = results.getMetaData();
                int numberCols = rsmd.getColumnCount();
                System.out.println("numberCols: "+numberCols);
                
                mainRecord = new Record[numberCols];           

                int i=0;
                while (results.next()) {
                    i++;
                    mainRecord[i] = new Record();
                    mainRecord[i].setCode(results.getString("CODE"));
                    mainRecord[i].setDescription(results.getString("DESCRIPTION"));
                    mainRecord[i].setOwnerName(results.getString("IDOWNER"));
                }                
                results.close();
                stmt.close();
            }
            catch (SQLException sqlExcept)
            {
                sqlExcept.printStackTrace();
            }
            
            return mainRecord;
        }

}
